#!/usr/bin/python
#
# Copyright (C) 2010 Google Inc.

""" Builds SQL strings.

Builds SQL strings to pass to FTClient query method.
"""

__author__ = 'kbrisbin@google.com (Kathryn Brisbin)'


import re

class SQL:
  """ Helper class for building SQL queries """

  def showTables(self):
    """ Build a SHOW TABLES sql statement.

    Returns:
      the sql statement
    """
    return 'SHOW TABLES'

  def describeTable(self, table_id):
    """ Build a DESCRIBE <tableid> sql statement.

    Args:
      table_id: the ID of the table to describe

    Returns:
      the sql statement
    """
    return 'DESCRIBE %d' % (table_id)

  def createTable(self, tableName, columns):
    """ Build a CREATE TABLE sql statement.

    Args:
      tableName: Name of the table to create
      columns: Dictionary of column names and column types to create as key and value pairs. Example:
        {
        "col_name1":"STRING",
        "col_name2":"NUMBER",
        "col_name3":"LOCATION",
        "col_name4":"DATETIME"
        }

    Returns:
      the sql statement
    """

    cols_and_datatypes = ",".join(["'%s': %s" % (col[0], col[1]) for col in columns.items()])
    return "CREATE TABLE '%s' (%s)" % (tableName, cols_and_datatypes)

  def createTables(self, tables):
    """ Build a CREATE TABLE sql statement.

    Args:
      table: a dictionary representing the table. example:
        {
          "tablename":
            {
            "col_name1":"STRING",
            "col_name2":"NUMBER",
            "col_name3":"LOCATION",
            "col_name4":"DATETIME"
            }
        }

    Returns:
      the sql statement
    """

    for table_name in tables.keys():
        cols_and_datatypes = ",".join(["'%s': %s" % (col[0], col[1]) for col in tables.get(table_name).items()])
        yield ( table_name, "CREATE TABLE '%s' (%s)" % (table_name, cols_and_datatypes))


  def select(self, table_id, cols=None, condition=None, groupByCols=None, orderByCols=None, orderByDirection='ASC', offset=0, limit=50):
    """ Build a SELECT sql statement.

    Args:
      table_id: the id of the table
      cols: a list of columns to return. If None, return all
      condition: a statement to add to the WHERE clause. For example,
        "age > 30" or "Name = 'Steve'". Use single quotes as per the API.

    Returns:
      the sql statement
    """
    stringCols = "*"
    if cols: stringCols = 'ROWID,' + ','.join(["'%s'" % (col) for col in cols])
    if condition: select = 'SELECT %s FROM %d WHERE %s' % (stringCols, table_id, condition)
    else: select = 'SELECT %s FROM %d' % (stringCols, table_id)
    
    if groupByCols: select = select + ' GROUP BY ' + ','.join(["'%s'" % (col) for col in groupByCols])
    
    if orderByCols: select = select + ' ORDER BY ' + ','.join(["'%s'" % (col) for col in orderByCols]) + ' ' + orderByDirection
        
    if offset: select = select + ' OFFSET %d' % offset
    select = select + ' LIMIT %d' % limit
    
    return select

  def updateTable(self, table_id, colValuesDict, row_id):
      return self.update(table_id, colValuesDict.keys(), colValuesDict.values(), row_id)

  def update(self, table_id, cols, values, row_id):
    """ Build an UPDATE sql statement.

    Args:
      table_id: the id of the table
      cols: the columns to update
      values: the new values
      row_id: the id of the row to update

    Returns:
      the sql statement
    """
    if len(cols) != len(values): return None
    updateStatement = ""
    count = 1
    for i in range(len(cols)):
      updateStatement = "%s'%s' = " % (updateStatement, cols[i])
      if type(values[i]).__name__ == 'int':
        updateStatement = "%s%d" % (updateStatement, values[i])
      elif type(values[i]).__name__ == 'float':
        updateStatement = "%s%f" % (updateStatement, values[i])
      else:
        updateStatement = "%s'%s'" % (updateStatement, re.sub(r"(?<!\\)'", "\\'", values[i]) )

      if count < len(cols): updateStatement = "%s," % (updateStatement)
      count += 1

    return "UPDATE %d SET %s WHERE ROWID = '%d'" % (table_id, updateStatement, row_id)

  def delete(self, table_id, row_id):
    """ Build DELETE sql statement.

    Args:
      table_id: the id of the table
      row_id: the id of the row to delete

    Returns:
      the sql statement
    """
    return "DELETE FROM %d WHERE ROWID = '%d'" % (table_id, row_id)

  def insertRows(self, table_id, rows):
    return ';'.join([self.insert(table_id, row) for row in rows])

  def insert(self, table_id, colvalues):
    """ Build an INSERT sql statement.

    Args:
      table_id: the id of the table
      values: dictionary of column to value. Example:
        {
        "col_name1":12,
        "col_name2":"mystring",
        "col_name3":"Mountain View",
        "col_name4":"9/10/2010"
        }

    Returns:
      the sql statement
    """
    stringValues = ""
    count = 1
    cols = []
    values = []
    for k,v in colvalues.items():
        if type(v) == int:
            cols.append(k)
            values.append(v)
        elif type(v) == float:
            cols.append(k)
            values.append(v)
        elif v:
            cols.append(k)
            values.append(v)
    #cols = values.keys()
    #values = values.values()
    for value in values:
        if type(value).__name__=='int':
          stringValues += str(value)
        elif type(value).__name__=='float':
          stringValues += str(value)
        else:
          stringValues += "'%s'" % (re.sub(r"(?<!\\)'", "\\'", value))
        if count < len(values): stringValues += ","
        count += 1

    return 'INSERT INTO %d (%s) VALUES (%s)' % (int(table_id), ','.join(["'%s'" % col for col in cols]), stringValues)

  def dropTable(self, table_id):
    """ Build DROP TABLE sql statement.

    Args:
      table_id: the id of the table

    Returns:
      the sql statement
    """
    return "DROP TABLE %d" % (table_id)


if __name__ == '__main__':
    pass



